package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.dto.AdRecoveryDto;
import com.dy.yunying.api.vo.AdRecoveryVo;
import com.pig4cloud.pig.common.core.constant.enums.PlanAttrStatTypeEnum;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

/**
 * @author ：lile
 * @date ：2021/6/22 11:04
 * @description：
 * @modified By：
 */
@Component
@Slf4j
public class AdRecoveryDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;
	private final static int[] NUMS_ = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 45, 60, 75, 90, 120, 150, 180};

	public List<AdRecoveryVo> selectAdRecoverySource(AdRecoveryDto req) {

		StringBuilder sql = getSql(req);


		log.info("回收分析.sql:[{}]", sql.toString());

		List<AdRecoveryVo> data = new ArrayList<>();
		SqlRowSet rs = clickhouseTemplate.queryForRowSet(sql.toString());

		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());

		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			AdRecoveryVo item = ObjByRow(rs, columns, "period", req.getShowRatio());
			data.add(item);
		}
		return data;
	}

	@SneakyThrows
	private void setRoi(Integer num, BigDecimal cost, Integer showRatio, AdRecoveryVo vo, SqlRowSet rs) {

		Field setR = vo.getClass().getDeclaredField(String.format("roi%s", num));
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("roi%sNum", num));
		setRN.setAccessible(true);
		//绑定数据到 roi1
		if (cost.equals(BigDecimal.ZERO)) {
			if (showRatio == 0) {
				String key = String.format("worth%s", num);
				BigDecimal worth = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, BigDecimal.ZERO);
				setRN.set(vo, worth);
			} else {
				String key = String.format("worthshar%s", num);
				BigDecimal worthshar = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, BigDecimal.ZERO);
				setRN.set(vo, worthshar);
			}
		} else {
			//不显示分成前数据
			if (showRatio == 0) {
				String key = String.format("worth%s", num);
				BigDecimal worth = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worth.divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
				setRN.set(vo, worth);
			} else {
				String key = String.format("worthshar%s", num);
				BigDecimal worthshar = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worthshar.divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
				setRN.set(vo, worthshar);
			}
		}
	}

	@SneakyThrows
	private void setLtv(Integer num, Integer divideNum, Integer showRatio, AdRecoveryVo vo, SqlRowSet rs) {
		Field setR = vo.getClass().getDeclaredField(String.format("ltv%s", num));
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("ltv%sNum", num));
		setRN.setAccessible(true);
		if (divideNum == 0 || divideNum == null) {
			if (showRatio == 0) {
				String key = String.format("worth%s", num);
				BigDecimal worth = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, BigDecimal.ZERO);
				setRN.set(vo, worth);
			} else {
				String key = String.format("worthshar%s", num);
				BigDecimal worthshar = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, BigDecimal.ZERO);
				setRN.set(vo, worthshar);
			}
		} else {
			//不显示分成前数据
			if (showRatio == 0) {
				String key = String.format("worth%s", num);
				BigDecimal worth = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worth.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
				setRN.set(vo, worth);
			} else {
				String key = String.format("worthshar%s", num);
				BigDecimal worthshar = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worthshar.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
				setRN.set(vo, worthshar);
			}
		}
	}

	@SneakyThrows
	private void setltvtimes(Integer num, BigDecimal worth1, BigDecimal worthshar1, Integer showRatio, AdRecoveryVo vo, SqlRowSet rs) {
		Field setR = vo.getClass().getDeclaredField(String.format("ltvtimes%s", num));
		setR.setAccessible(true);
		Field setRN = vo.getClass().getDeclaredField(String.format("ltvtimes%sNum", num));
		setRN.setAccessible(true);
		if (worth1.equals(BigDecimal.ZERO)) {
			setR.set(vo, BigDecimal.ZERO);
			setRN.set(vo, BigDecimal.ZERO);
		}
		if (worthshar1.equals(BigDecimal.ZERO)) {
			setR.set(vo, BigDecimal.ZERO);
			setRN.set(vo, BigDecimal.ZERO);
		} else {
			//不显示分成前数据
			if (showRatio == 0) {
				String key = String.format("worth%s", num);
				BigDecimal worth = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worth.divide(worth1, 2, RoundingMode.HALF_UP));
				setRN.set(vo, worth);
			} else {
				String key = String.format("worthshar%s", num);
				BigDecimal worthshar = rs.getBigDecimal(key) == null ? new BigDecimal(0) : rs.getBigDecimal(key);
				setR.set(vo, worthshar.divide(worthshar1, 2, RoundingMode.HALF_UP));
				setRN.set(vo, worthshar);
			}
		}
	}

	private AdRecoveryVo ObjByRow(SqlRowSet rs, List<String> columns, String type, Integer showRatio) {


		AdRecoveryVo vo = new AdRecoveryVo();
		if (type.equals("period")) {
			vo.setPeriod(rs.getString("period"));
		}

		if (columns.contains("cost"))
			vo.setCost(rs.getBigDecimal("cost"));

		if (rs.getBigDecimal("rudecost") == null) {
			vo.setRudecost(BigDecimal.ZERO);
		} else {
			vo.setRudecost(rs.getBigDecimal("rudecost").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("rudecost"));
		}

		//投放人id
		if (columns.contains("investor") && StringUtils.isNotBlank(rs.getString("investor"))) {
			vo.setInvestor(rs.getString("investor"));
		} else {
			vo.setInvestor("-");
		}

		//投放人名称
		if (columns.contains("investorName") && StringUtils.isNotBlank(rs.getString("investorName"))) {
			vo.setInvestorName(rs.getString("investorName"));
		} else {
			vo.setInvestorName("-");
		}


		//部门维度 部门名称
		if (columns.contains("deptName") && StringUtils.isNotBlank(rs.getString("deptName"))) {
			vo.setDeptName(rs.getString("deptName"));
		} else {
			vo.setDeptName("-");
		}

		//部门维度 部门id
		if (columns.contains("deptId") && StringUtils.isNotBlank(rs.getString("deptId"))) {
			vo.setDeptId(rs.getString("deptId"));
		} else {
			vo.setDeptId("-");
		}

		//组别维度 部门名称
		if (columns.contains("userGroupName") && StringUtils.isNotBlank(rs.getString("userGroupName"))) {
			vo.setUserGroupName(rs.getString("userGroupName"));
		} else {
			vo.setUserGroupName("-");
		}

		//组别维度 部门id
		if (columns.contains("userGroupId") && StringUtils.isNotBlank(rs.getString("userGroupId"))) {
			vo.setUserGroupId(rs.getString("userGroupId"));
		} else {
			vo.setUserGroupId("-");
		}
		//主渠道
		if (columns.contains("parentchl") && StringUtils.isNotBlank(rs.getString("parentchl"))) {
			vo.setParentchl(rs.getString("parentchl"));
		} else {
			vo.setParentchl("-");
		}

		//父游戏 id
		if (columns.contains("pgid"))
			vo.setPgid(rs.getLong("pgid"));

		//子游戏 id
		if (columns.contains(PlanAttrStatTypeEnum.GAMEID.V()))
			vo.setGameid(rs.getLong("gameid"));
		if (columns.contains(PlanAttrStatTypeEnum.GAMEID.V()) && StringUtils.isNotBlank(rs.getString("gName"))) {
			vo.setGname(rs.getString("gName"));
		} else {
			vo.setGname("-");
		}
		//系统
		if (columns.contains("os"))
			vo.setOs(rs.getInt("os"));

		//分包
		if (columns.contains("appchl") && StringUtils.isNotBlank(rs.getString("appchl"))) {
			vo.setAppchl(rs.getString("appchl"));
		} else {
			vo.setAppchl("-");
		}

		// 新增设备充值数
		Integer divideNum = Integer.valueOf(
				rs.getInt("usrnamenums")
		);
		BigDecimal cost = BigDecimal.ZERO;
		if (rs.getBigDecimal("cost") == null) {
			cost = BigDecimal.ZERO;
			vo.setCost(cost);
		} else {
			cost = rs.getBigDecimal("cost").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("cost");
			vo.setCost(cost);
		}

		if (columns.contains("usrnamenums"))
			vo.setUsrnamenums(rs.getInt("usrnamenums"));


		//新增设备成本
		if (divideNum == 0 || divideNum == null) {
			vo.setDeviceCose(BigDecimal.ZERO);
		} else {
			vo.setDeviceCose(cost.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
		}

		//累计ROI
		if (cost.equals(BigDecimal.ZERO)) {
			vo.setRoiRatio(BigDecimal.ZERO);
			vo.setRoiRatioNum(BigDecimal.ZERO);
		} else {
			//不显示分成前数据
			if (showRatio == 0) {
				BigDecimal worth = rs.getBigDecimal("worth") == null ? BigDecimal.ZERO : rs.getBigDecimal("worth");
				vo.setRoiRatio(worth.divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
				vo.setRoiRatioNum(worth);
			} else {
				BigDecimal worthshar = rs.getBigDecimal("worthshar") == null ? BigDecimal.ZERO : rs.getBigDecimal("worthshar");
				vo.setRoiRatio(worthshar.divide(cost, 4, RoundingMode.HALF_UP).multiply(new BigDecimal(100)));
				vo.setRoiRatioNum(worthshar);
			}
		}
		for (int i : NUMS_) {
			setRoi(i, cost, showRatio, vo, rs);
		}


		// 累计LTV
		if (divideNum == 0 || divideNum == null) {
			vo.setLtvRatio(BigDecimal.ZERO);
		} else {
			//不显示分成前数据
			if (showRatio == 0) {
				BigDecimal worth = rs.getBigDecimal("worth") == null ? BigDecimal.ZERO : rs.getBigDecimal("worth");
				vo.setLtvRatio(worth.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
			} else {
				BigDecimal worthshar = rs.getBigDecimal("worthshar") == null ? BigDecimal.ZERO : rs.getBigDecimal("worthshar");
				vo.setLtvRatio(worthshar.divide(new BigDecimal(divideNum), 2, RoundingMode.HALF_UP));
			}
		}
		for (int i : NUMS_) {
			setLtv(i, divideNum, showRatio, vo, rs);
		}


		BigDecimal worth1 = BigDecimal.ZERO;
		if (rs.getBigDecimal("worth1") == null) {
			worth1 = BigDecimal.ZERO;
		} else {
			worth1 = rs.getBigDecimal("worth1").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("worth1");
		}
		BigDecimal worthshar1 = BigDecimal.ZERO;
		if (rs.getBigDecimal("worthshar1") == null) {
			worthshar1 = BigDecimal.ZERO;
		} else {
			worthshar1 = rs.getBigDecimal("worthshar1").intValue() == 0.000 ? BigDecimal.ZERO : rs.getBigDecimal("worthshar1");
		}
		for (int i : NUMS_) {
			setltvtimes(i, worth1, worthshar1, showRatio, vo, rs);
		}

		return vo;
	}

	// 每个指标汇总sql
	private StringBuilder getSql(AdRecoveryDto req) {
		StringBuilder qyery_sql = new StringBuilder();
		qyery_sql.append(" SELECT \n ");
		qyery_sql.append(this.getQueryColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			qyery_sql.append(" gName,");
		}
		qyery_sql.append(" cost,\n" +
				" rudecost,\n" +
				" usrnamenums,\n" +
				" worth,\n" +
				" worth1,\n" +
				" worth2,\n" +
				" worth3,\n" +
				" worth4,\n" +
				" worth5,\n" +
				" worth6,\n" +
				" worth7,\n" +
				" worth8,\n" +
				" worth9,\n" +
				" worth10,\n" +
				" worth11,\n" +
				" worth12,\n" +
				" worth13,\n" +
				" worth14,\n" +
				" worth15,\n" +
				" worth16,\n" +
				" worth17,\n" +
				" worth18,\n" +
				" worth19,\n" +
				" worth20,\n" +
				" worth21,\n" +
				" worth22,\n" +
				" worth23,\n" +
				" worth24,\n" +
				" worth25,\n" +
				" worth26,\n" +
				" worth27,\n" +
				" worth28,\n" +
				" worth29,\n" +
				" worth30,\n" +
				" worth45,\n" +
				" worth60,\n" +
				" worth75,\n" +
				" worth90,\n" +
				" worth120,\n" +
				" worth150,\n" +
				" worth180,\n" +
				" worthshar,\n" +
				" worthshar1,\n" +
				" worthshar2,\n" +
				" worthshar3,\n" +
				" worthshar4,\n" +
				" worthshar5,\n" +
				" worthshar6,\n" +
				" worthshar7,\n" +
				" worthshar8,\n" +
				" worthshar9,\n" +
				" worthshar10,\n" +
				" worthshar11,\n" +
				" worthshar12,\n" +
				" worthshar13,\n" +
				" worthshar14,\n" +
				" worthshar15,\n" +
				" worthshar16,\n" +
				" worthshar17,\n" +
				" worthshar18,\n" +
				" worthshar19,\n" +
				" worthshar20,\n" +
				" worthshar21,\n" +
				" worthshar22,\n" +
				" worthshar23,\n" +
				" worthshar24,\n" +
				" worthshar25,\n" +
				" worthshar26,\n" +
				" worthshar27,\n" +
				" worthshar28,\n" +
				" worthshar29,\n" +
				" worthshar30,\n" +
				" worthshar45,\n" +
				" worthshar60,\n" +
				" worthshar75,\n" +
				" worthshar90,\n" +
				" worthshar120,\n" +
				" worthshar150,\n" +
				" worthshar180 \n"
		);
		qyery_sql.append(" from ( \n ");
		qyery_sql.append(" SELECT \n");
		qyery_sql.append(this.getQueryColumnSql(req));
		if (StringUtils.isNotBlank(req.getPeriod())) {
			if (!req.getPeriod().equals("day")) {
				qyery_sql.append(" d3.cost cost,\n" +
						"d3.rudecost rudecost,\n" +
						"d0102.usrnamenums usrnamenums, \n ");
				qyery_sql.append(" dateDiff('day', parseDateTimeBestEffort(toString( ").append(req.getSTime()).append(" )),  today() ) as niff1, \n ");
				qyery_sql.append(" d0102.worth worth,\n" +
						"multiIf(niff1<0 , NULL, d0102.worth1) worth1,\n" +
						"multiIf(niff1<1,NULL, d0102.worth2) worth2,\n" +
						"multiIf(niff1<2,NULL, d0102.worth3) worth3,\n" +
						"multiIf(niff1<3,NULL, d0102.worth4) worth4,\n" +
						"multiIf(niff1<4,NULL, d0102.worth5) worth5,\n" +
						"multiIf(niff1<5,NULL, d0102.worth6) worth6,\n" +
						"multiIf(niff1<6,NULL, d0102.worth7) worth7,\n" +
						"multiIf(niff1<7,NULL, d0102.worth8) worth8,\n" +
						"multiIf(niff1<8,NULL, d0102.worth9) worth9,\n" +
						"multiIf(niff1<9,NULL, d0102.worth10) worth10,\n" +
						"multiIf(niff1<10,NULL,d0102.worth11) worth11,\n" +
						"multiIf(niff1<11,NULL,d0102.worth12) worth12,\n" +
						"multiIf(niff1<12,NULL,d0102.worth13) worth13,\n" +
						"multiIf(niff1<13,NULL,d0102.worth14) worth14,\n" +
						"multiIf(niff1<14,NULL,d0102.worth15) worth15,\n" +
						"multiIf(niff1<15,NULL,d0102.worth16) worth16,\n" +
						"multiIf(niff1<16,NULL,d0102.worth17) worth17,\n" +
						"multiIf(niff1<17,NULL,d0102.worth18) worth18,\n" +
						"multiIf(niff1<18,NULL,d0102.worth19) worth19,\n" +
						"multiIf(niff1<19,NULL,d0102.worth20) worth20,\n" +
						"multiIf(niff1<20,NULL,d0102.worth21) worth21,\n" +
						"multiIf(niff1<21,NULL,d0102.worth22) worth22,\n" +
						"multiIf(niff1<22,NULL,d0102.worth23) worth23,\n" +
						"multiIf(niff1<23,NULL,d0102.worth24) worth24,\n" +
						"multiIf(niff1<24,NULL,d0102.worth25) worth25,\n" +
						"multiIf(niff1<25,NULL,d0102.worth26) worth26,\n" +
						"multiIf(niff1<26,NULL,d0102.worth27) worth27,\n" +
						"multiIf(niff1<27,NULL,d0102.worth28) worth28,\n" +
						"multiIf(niff1<28,NULL,d0102.worth29) worth29,\n" +
						"multiIf(niff1<29,NULL,d0102.worth30) worth30,\n" +
						"multiIf(niff1<44,NULL,d0102.worth45) worth45,\n" +
						"multiIf(niff1<59,NULL,d0102.worth60) worth60,\n" +
						"multiIf(niff1<74,NULL,d0102.worth75) worth75,\n" +
						"multiIf(niff1<89,NULL,d0102.worth90) worth90,\n" +
						"multiIf(niff1<119,NULL, d0102.worth120) worth120,\n" +
						"multiIf(niff1<149,NULL, d0102.worth150) worth150,\n" +
						"multiIf(niff1<179,NULL, d0102.worth180) worth180,\n" +
						"d0102.worthshar worthshar,\n" +
						"multiIf(niff1<0 ,NULL, d0102.worthshar1) worthshar1,\n" +
						"multiIf(niff1<1 ,NULL, d0102.worthshar2) worthshar2,\n" +
						"multiIf(niff1<2 ,NULL, d0102.worthshar3) worthshar3,\n" +
						"multiIf(niff1<3 ,NULL, d0102.worthshar4) worthshar4,\n" +
						"multiIf(niff1<4 ,NULL, d0102.worthshar5) worthshar5,\n" +
						"multiIf(niff1<5 ,NULL, d0102.worthshar6) worthshar6,\n" +
						"multiIf(niff1<6 ,NULL, d0102.worthshar7) worthshar7,\n" +
						"multiIf(niff1<7 ,NULL, d0102.worthshar8) worthshar8,\n" +
						"multiIf(niff1<8 ,NULL, d0102.worthshar9) worthshar9,\n" +
						"multiIf(niff1<9 ,NULL, d0102.worthshar10) worthshar10,\n" +
						"multiIf(niff1<10 ,NULL, d0102.worthshar11) worthshar11,\n" +
						"multiIf(niff1<11 ,NULL, d0102.worthshar12) worthshar12,\n" +
						"multiIf(niff1<12 ,NULL, d0102.worthshar13) worthshar13,\n" +
						"multiIf(niff1<13 ,NULL, d0102.worthshar14) worthshar14,\n" +
						"multiIf(niff1<14 ,NULL, d0102.worthshar15) worthshar15,\n" +
						"multiIf(niff1<15 ,NULL, d0102.worthshar16) worthshar16,\n" +
						"multiIf(niff1<16 ,NULL, d0102.worthshar17) worthshar17,\n" +
						"multiIf(niff1<17 ,NULL, d0102.worthshar18) worthshar18,\n" +
						"multiIf(niff1<18 ,NULL, d0102.worthshar19) worthshar19,\n" +
						"multiIf(niff1<19 ,NULL, d0102.worthshar20) worthshar20,\n" +
						"multiIf(niff1<20 ,NULL, d0102.worthshar21) worthshar21,\n" +
						"multiIf(niff1<21 ,NULL, d0102.worthshar22) worthshar22,\n" +
						"multiIf(niff1<22 ,NULL, d0102.worthshar23) worthshar23,\n" +
						"multiIf(niff1<23 ,NULL, d0102.worthshar24) worthshar24,\n" +
						"multiIf(niff1<24 ,NULL, d0102.worthshar25) worthshar25,\n" +
						"multiIf(niff1<25 ,NULL, d0102.worthshar26) worthshar26,\n" +
						"multiIf(niff1<26 ,NULL, d0102.worthshar27) worthshar27,\n" +
						"multiIf(niff1<27 ,NULL, d0102.worthshar28) worthshar28,\n" +
						"multiIf(niff1<28 ,NULL, d0102.worthshar29) worthshar29,\n" +
						"multiIf(niff1<29 ,NULL, d0102.worthshar30) worthshar30,\n" +
						"multiIf(niff1<44 ,NULL, d0102.worthshar45) worthshar45,\n" +
						"multiIf(niff1<59 ,NULL, d0102.worthshar60) worthshar60,\n" +
						"multiIf(niff1<74 ,NULL, d0102.worthshar75) worthshar75,\n" +
						"multiIf(niff1<89 ,NULL, d0102.worthshar90) worthshar90,\n" +
						"multiIf(niff1<119 ,NULL, d0102.worthshar120) worthshar120,\n" +
						"multiIf(niff1<149 ,NULL, d0102.worthshar150) worthshar150,\n" +
						"multiIf(niff1<179 ,NULL, d0102.worthshar180) worthshar180  ");
			} else {
				qyery_sql.append(
						"d3.cost cost,\n" +
								"d3.rudecost rudecost,\n" +
								"d0102.usrnamenums usrnamenums,\n" +
								"d0102.worth worth,\n" +
								"d0102.worth1 worth1,\n" +
								"d0102.worth2 worth2,\n" +
								"d0102.worth3 worth3,\n" +
								"d0102.worth4 worth4,\n" +
								"d0102.worth5 worth5,\n" +
								"d0102.worth6 worth6,\n" +
								"d0102.worth7 worth7,\n" +
								"d0102.worth8 worth8,\n" +
								"d0102.worth9 worth9,\n" +
								"d0102.worth10 worth10,\n" +
								"d0102.worth11 worth11,\n" +
								"d0102.worth12 worth12,\n" +
								"d0102.worth13 worth13,\n" +
								"d0102.worth14 worth14,\n" +
								"d0102.worth15 worth15,\n" +
								"d0102.worth16 worth16,\n" +
								"d0102.worth17 worth17,\n" +
								"d0102.worth18 worth18,\n" +
								"d0102.worth19 worth19,\n" +
								"d0102.worth20 worth20,\n" +
								"d0102.worth21 worth21,\n" +
								"d0102.worth22 worth22,\n" +
								"d0102.worth23 worth23,\n" +
								"d0102.worth24 worth24,\n" +
								"d0102.worth25 worth25,\n" +
								"d0102.worth26 worth26,\n" +
								"d0102.worth27 worth27,\n" +
								"d0102.worth28 worth28,\n" +
								"d0102.worth29 worth29,\n" +
								"d0102.worth30 worth30,\n" +
								"d0102.worth45 worth45,\n" +
								"d0102.worth60 worth60,\n" +
								"d0102.worth75 worth75,\n" +
								"d0102.worth90 worth90,\n" +
								"d0102.worth120 worth120,\n" +
								"d0102.worth150 worth150,\n" +
								"d0102.worth180 worth180,\n" +
								"d0102.worthshar worthshar,\n" +
								"d0102.worthshar1 worthshar1,\n" +
								"d0102.worthshar2 worthshar2,\n" +
								"d0102.worthshar3 worthshar3,\n" +
								"d0102.worthshar4 worthshar4,\n" +
								"d0102.worthshar5 worthshar5,\n" +
								"d0102.worthshar6 worthshar6,\n" +
								"d0102.worthshar7 worthshar7,\n" +
								"d0102.worthshar8 worthshar8,\n" +
								"d0102.worthshar9 worthshar9,\n" +
								"d0102.worthshar10 worthshar10,\n" +
								"d0102.worthshar11 worthshar11,\n" +
								"d0102.worthshar12 worthshar12,\n" +
								"d0102.worthshar13 worthshar13,\n" +
								"d0102.worthshar14 worthshar14,\n" +
								"d0102.worthshar15 worthshar15,\n" +
								"d0102.worthshar16 worthshar16,\n" +
								"d0102.worthshar17 worthshar17,\n" +
								"d0102.worthshar18 worthshar18,\n" +
								"d0102.worthshar19 worthshar19,\n" +
								"d0102.worthshar20 worthshar20,\n" +
								"d0102.worthshar21 worthshar21,\n" +
								"d0102.worthshar22 worthshar22,\n" +
								"d0102.worthshar23 worthshar23,\n" +
								"d0102.worthshar24 worthshar24,\n" +
								"d0102.worthshar25 worthshar25,\n" +
								"d0102.worthshar26 worthshar26,\n" +
								"d0102.worthshar27 worthshar27,\n" +
								"d0102.worthshar28 worthshar28,\n" +
								"d0102.worthshar29 worthshar29,\n" +
								"d0102.worthshar30 worthshar30,\n" +
								"d0102.worthshar45 worthshar45,\n" +
								"d0102.worthshar60 worthshar60,\n" +
								"d0102.worthshar75 worthshar75,\n" +
								"d0102.worthshar90 worthshar90,\n" +
								"d0102.worthshar120 worthshar120,\n" +
								"d0102.worthshar150 worthshar150,\n" +
								"d0102.worthshar180 worthshar180 \n");
			}
		}
		qyery_sql.append(" FROM \n");
		qyery_sql.append(" ( \n");
		qyery_sql.append(" SELECT \n");
		qyery_sql.append(this.getQueryColumnSql(req));
//		qyery_sql.append(" d1.day, \n");
		qyery_sql.append(" d1.uuidnums,\n" +
				"\t d1.usrnamenums,\n" +
				"\t d2.worth,\n" +
				"\t d2.worth1,\n" +
				"     d2.worth1,\n" +
				"     d2.worth2,\n" +
				"     d2.worth3,\n" +
				"     d2.worth4,\n" +
				"     d2.worth5,\n" +
				"     d2.worth6,\n" +
				"     d2.worth7,\n" +
				"     d2.worth8,\n" +
				"     d2.worth9,\n" +
				"\t d2.worth10,\n" +
				"\t d2.worth11,\n" +
				"     d2.worth12,\n" +
				"     d2.worth13,\n" +
				"     d2.worth14,\n" +
				"     d2.worth15,\n" +
				"     d2.worth16,\n" +
				"     d2.worth17,\n" +
				"     d2.worth18,\n" +
				"     d2.worth19,\n" +
				"     d2.worth20,\n" +
				"     d2.worth21,\n" +
				"     d2.worth22,\n" +
				"     d2.worth23,\n" +
				"     d2.worth24,\n" +
				"     d2.worth25,\n" +
				"     d2.worth26,\n" +
				"     d2.worth27,\n" +
				"     d2.worth28,\n" +
				"     d2.worth29,\n" +
				"     d2.worth30,\n" +
				"     d2.worth45,\n" +
				"     d2.worth60,\n" +
				"     d2.worth75,\n" +
				"     d2.worth90,\n" +
				"\t d2.worth120,\n" +
				"     d2.worth150,\n" +
				"     d2.worth180,\n" +
				"     d2.worthshar,\n" +
				"     d2.worthshar1,\n" +
				"     d2.worthshar2,\n" +
				"     d2.worthshar3,\n" +
				"     d2.worthshar4,\n" +
				"     d2.worthshar5,\n" +
				"     d2.worthshar6,\n" +
				"     d2.worthshar7,\n" +
				"     d2.worthshar8,\n" +
				"     d2.worthshar9,\n" +
				"\t d2.worthshar10,\n" +
				"\t d2.worthshar11,\n" +
				"     d2.worthshar12,\n" +
				"     d2.worthshar13,\n" +
				"     d2.worthshar14,\n" +
				"     d2.worthshar15,\n" +
				"     d2.worthshar16,\n" +
				"     d2.worthshar17,\n" +
				"     d2.worthshar18,\n" +
				"     d2.worthshar19,\n" +
				"     d2.worthshar20,\n" +
				"     d2.worthshar21,\n" +
				"     d2.worthshar22,\n" +
				"     d2.worthshar23,\n" +
				"     d2.worthshar24,\n" +
				"     d2.worthshar25,\n" +
				"     d2.worthshar26,\n" +
				"     d2.worthshar27,\n" +
				"     d2.worthshar28,\n" +
				"     d2.worthshar29,\n" +
				"     d2.worthshar30,\n" +
				"     d2.worthshar45,\n" +
				"     d2.worthshar60,\n" +
				"     d2.worthshar75,\n" +
				"     d2.worthshar90,\n" +
				"\t d2.worthshar120,\n" +
				"     d2.worthshar150,\n" +
				"     d2.worthshar180 \n");
		qyery_sql.append(" from ( \n");
		qyery_sql.append(this.getDeviceSql(req));
		qyery_sql.append(" ) d1 \n");
		qyery_sql.append(" FULL JOIN \n");
		qyery_sql.append(" ( \n");
		qyery_sql.append(this.getPayNSql(req));
		qyery_sql.append(" )d2 \n");
		qyery_sql.append(" USING ( ");
		qyery_sql.append(this.getGroupColumnSql(req));
		qyery_sql.append("  ) \n");
		qyery_sql.append(" )d0102 \n");
		qyery_sql.append(" FULL JOIN \n");
		qyery_sql.append(" ( \n");
		qyery_sql.append(this.getCostSql(req));
		qyery_sql.append(" )d3 \n");
		qyery_sql.append(" USING ( ");
		qyery_sql.append(this.getGroupColumnSql(req));
		qyery_sql.append("  ) \n");
		qyery_sql.append("  ) d1000 \n");
		if (StringUtils.isNotBlank(req.getQueryColumn()) && req.getQueryColumn().contains(PlanAttrStatTypeEnum.GAMEID.V())) {
			qyery_sql.append("     LEFT JOIN (");
			qyery_sql.append("     SELECT");
			qyery_sql.append("     t050.id gameid,");
			qyery_sql.append("     t050.gname gName");
			qyery_sql.append("     FROM odsmysql_wan_game t050");
			qyery_sql.append("     ) t2000");
			qyery_sql.append("	ON d1000.gameid = t2000.gameid");
		}
		qyery_sql.append(" where 1=1\n");
		qyery_sql.append(" ORDER BY period DESC");
		return qyery_sql;
	}


	//	消耗、返点后消耗
	private StringBuilder getCostSql(AdRecoveryDto req) {
		StringBuilder costSql = new StringBuilder();
		costSql.append(" SELECT \n");
		//添加查询维度
		costSql.append(this.getQueryColumnSql(req));

		costSql.append(" SUM(cost) cost, --返点后消耗 \n ");
		costSql.append(" SUM(rudecost) rudecost, --消耗 \n ");
		costSql.append(" SUM(adshow) shownums, --展示数 \n ");
		costSql.append(" sum(click) clicknums --点击数 \n ");
		costSql.append(" from ( ");
		costSql.append(" SELECT  \n");
		costSql.append(" b.date  day,  \n");
		costSql.append(this.getPeriodSql(req));
		costSql.append(" IFNULL(g.pgid, 0) pgid, \n");
		costSql.append(" IFNULL(g.os, 0) os, \n");
		costSql.append(" IFNULL(reg.gameid, 0) gameid, \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.appchl else '-' end appchl,   \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.parentchl else '-' end parentchl,  \n");
		costSql.append(" case when reg.adid is not null and reg.adid !='' then reg.chl else '-' end chl,  \n");
		costSql.append(" osu.user_id investor,  \n");
		costSql.append(" osu.real_name investorName,  \n");
		costSql.append(" osu.dept_id deptId,  \n");
		costSql.append(" IFNULL(osu.dept_group_id,0) userGroupId, \n"); // 组别
		costSql.append(" IFNULL(osdg.name,'') userGroupName, \n"); // 组别名
		costSql.append(" osd.name deptName,  \n");
		costSql.append(" b.ad_id adid,  \n");
		costSql.append(" b.adconvert adconvert,  \n");
		costSql.append(" b.ad_account ad_account,  \n");
		costSql.append(" b.adshow adshow,  \n");
		costSql.append(" b.click click,  \n");
		costSql.append(" b.rudecost rudecost,  \n");
		costSql.append(" b.cost cost \n");
		costSql.append(" from  v_adid_rebate_day b \n");
		costSql.append(" left JOIN ad_ptype reg \n");
		costSql.append(" on reg.adid = b.ad_id \n");
		costSql.append(" left JOIN v_odsmysql_wan_game g \n");
		costSql.append(" on reg.gameid=g.id \n");
		costSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n ");
		costSql.append(" ON wpc.isdelete = 0\n");
		costSql.append(" AND wpc.parent_code = reg.parentchl \n");
		costSql.append(" AND wpc.chncode = reg.chl \n");


		costSql.append(" LEFT JOIN odsmysql_ad_account aa ON \n");
		costSql.append(" aa.advertiser_id = b.ad_account \n");
		costSql.append(" LEFT JOIN odsmysql_sys_user osu ON \n");
		costSql.append(" aa.throw_user = toString(osu.user_id) \n");
		costSql.append("  left join odsmysql_sys_dept osd \n");
		costSql.append("   on osu.dept_id = osd.dept_id \n");
		costSql.append("  left join odsmysql_sys_dept_group osdg \n");
		costSql.append("   on osu.dept_group_id = osdg.id \n");

		costSql.append(" LEFT JOIN v_odsmysql_adid vad  -- 广告计划表 \n");
		costSql.append(" ON b.ad_id = vad.adid \n");
		costSql.append(" AND b.ctype = vad.ctype \n");
		costSql.append(" WHERE 1=1 \n");
		costSql.append(" and  b.date >= ").append(req.getSTime());
		costSql.append(" and  b.date <= ").append(req.getETime());
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 广告权限
			costSql.append("  AND b.ad_account IN (  \n");
			costSql.append(req.getAdAccounts());
			costSql.append("  ) \n");
		}

		costSql.append("  ) \n");
		costSql.append(" WHERE 1=1 \n");
		costSql.append(this.selectCondition(req, " "));
		//添加查询维度
		costSql.append(" group by ");
		costSql.append(this.getGroupColumnSql(req));
		return costSql;
	}

	//计算N日充值金额
	private StringBuilder getPayNSql(AdRecoveryDto req) {
		StringBuilder payNSql = new StringBuilder();
		payNSql.append(" SELECT \n");
		payNSql.append(this.getQueryColumnSql(req));
		if (StringUtils.isNotBlank(req.getPeriod())) {
			if (!req.getPeriod().equals("day")) {
				payNSql.append(" SUM(fee + givemoney) worth,\n ");
				payNSql.append(" SUM(multiIf(pay_diff >= 0 and pay_diff <= 0, fee + givemoney, 0)) worth1,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 1, fee + givemoney, 0)) worth2,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 2, fee + givemoney, 0)) worth3,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 3, fee + givemoney, 0)) worth4,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 4, fee + givemoney, 0)) worth5,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 5, fee + givemoney, 0)) worth6,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 6, fee + givemoney, 0)) worth7,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 7, fee + givemoney, 0)) worth8,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 8, fee + givemoney, 0)) worth9,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 9, fee + givemoney, 0)) worth10,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 10, fee + givemoney, 0)) worth11,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 11, fee + givemoney, 0)) worth12,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 12, fee + givemoney, 0)) worth13,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 13, fee + givemoney, 0)) worth14,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 14, fee + givemoney, 0)) worth15,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 15, fee + givemoney, 0)) worth16,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 16, fee + givemoney, 0)) worth17,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 17, fee + givemoney, 0)) worth18,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 18, fee + givemoney, 0)) worth19,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 19, fee + givemoney, 0)) worth20,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 20, fee + givemoney, 0)) worth21,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 21, fee + givemoney, 0)) worth22,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 22, fee + givemoney, 0)) worth23,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 23, fee + givemoney, 0)) worth24,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 24, fee + givemoney, 0)) worth25,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 25, fee + givemoney, 0)) worth26,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 26, fee + givemoney, 0)) worth27,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 27, fee + givemoney, 0)) worth28,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 28, fee + givemoney, 0)) worth29,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 29, fee + givemoney, 0)) worth30,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 44, fee + givemoney, 0)) worth45,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 59, fee + givemoney, 0)) worth60,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 74, fee + givemoney, 0)) worth75,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 89, fee + givemoney, 0)) worth90,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 119, fee + givemoney, 0)) worth120,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 149, fee + givemoney, 0)) worth150,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 179, fee + givemoney, 0)) worth180,  \n" +
						"      SUM(sharfee) worthshar,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 0, sharfee, 0)) worthshar1,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 1, sharfee, 0)) worthshar2,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 2, sharfee, 0)) worthshar3,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 3, sharfee, 0)) worthshar4,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 4, sharfee, 0)) worthshar5,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 5, sharfee, 0)) worthshar6,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 6, sharfee, 0)) worthshar7,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 7, sharfee, 0)) worthshar8,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 8, sharfee, 0)) worthshar9,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 9, sharfee, 0)) worthshar10,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 10, sharfee, 0)) worthshar11,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 11, sharfee, 0)) worthshar12,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 12, sharfee, 0)) worthshar13,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 13, sharfee, 0)) worthshar14,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 14, sharfee, 0)) worthshar15,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 15, sharfee, 0)) worthshar16,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 16, sharfee, 0)) worthshar17,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 17, sharfee, 0)) worthshar18,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 18, sharfee, 0)) worthshar19,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 19, sharfee, 0)) worthshar20,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 20, sharfee, 0)) worthshar21,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 21, sharfee, 0)) worthshar22,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 22, sharfee, 0)) worthshar23,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 23, sharfee, 0)) worthshar24,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 24, sharfee, 0)) worthshar25,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 25, sharfee, 0)) worthshar26,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 26, sharfee, 0)) worthshar27,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 27, sharfee, 0)) worthshar28,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 28, sharfee, 0)) worthshar29,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 29, sharfee, 0)) worthshar30,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 44, sharfee, 0)) worthshar45,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 59, sharfee, 0)) worthshar60,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 74, sharfee, 0)) worthshar75,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 89, sharfee, 0)) worthshar90,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 119, sharfee, 0)) worthshar120,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 149, sharfee, 0)) worthshar150,\n" +
						"      SUM(multiIf(pay_diff >= 0 and pay_diff <= 179, sharfee, 0)) worthshar180 ");
			} else {
				payNSql.append(" SUM(fee + givemoney) worth,\n");
				payNSql.append(" SUM(multiIf(niff<0 , NULL, pay_diff >= 0 and pay_diff <= 0, fee + givemoney, 0)) worth1,\n" +
						"      SUM(multiIf(niff<1 , NULL, pay_diff >= 0 and pay_diff <= 1, fee + givemoney, 0)) worth2,\n" +
						"      SUM(multiIf(niff<2 , NULL, pay_diff >= 0 and pay_diff <= 2, fee + givemoney, 0)) worth3,\n" +
						"      SUM(multiIf(niff<3 , NULL, pay_diff >= 0 and pay_diff <= 3, fee + givemoney, 0)) worth4,\n" +
						"      SUM(multiIf(niff<4 , NULL, pay_diff >= 0 and pay_diff <= 4, fee + givemoney, 0)) worth5,\n" +
						"      SUM(multiIf(niff<5 , NULL, pay_diff >= 0 and pay_diff <= 5, fee + givemoney, 0)) worth6,\n" +
						"      SUM(multiIf(niff<6 , NULL, pay_diff >= 0 and pay_diff <= 6, fee + givemoney, 0)) worth7,\n" +
						"      SUM(multiIf(niff<7 , NULL, pay_diff >= 0 and pay_diff <= 7, fee + givemoney, 0)) worth8,\n" +
						"      SUM(multiIf(niff<8 , NULL, pay_diff >= 0 and pay_diff <= 8, fee + givemoney, 0)) worth9,\n" +
						"      SUM(multiIf(niff<9 , NULL, pay_diff >= 0 and pay_diff <= 9, fee + givemoney, 0)) worth10,\n" +
						"      SUM(multiIf(niff<10 , NULL, pay_diff >= 0 and pay_diff <= 10, fee + givemoney, 0)) worth11,\n" +
						"      SUM(multiIf(niff<11 , NULL, pay_diff >= 0 and pay_diff <= 11, fee + givemoney, 0)) worth12,\n" +
						"      SUM(multiIf(niff<12 , NULL, pay_diff >= 0 and pay_diff <= 12, fee + givemoney, 0)) worth13,\n" +
						"      SUM(multiIf(niff<13 , NULL, pay_diff >= 0 and pay_diff <= 13, fee + givemoney, 0)) worth14,\n" +
						"      SUM(multiIf(niff<14 , NULL, pay_diff >= 0 and pay_diff <= 14, fee + givemoney, 0)) worth15,\n" +
						"      SUM(multiIf(niff<15 , NULL, pay_diff >= 0 and pay_diff <= 15, fee + givemoney, 0)) worth16,\n" +
						"      SUM(multiIf(niff<16 , NULL, pay_diff >= 0 and pay_diff <= 16, fee + givemoney, 0)) worth17,\n" +
						"      SUM(multiIf(niff<17 , NULL, pay_diff >= 0 and pay_diff <= 17, fee + givemoney, 0)) worth18,\n" +
						"      SUM(multiIf(niff<18 , NULL, pay_diff >= 0 and pay_diff <= 18, fee + givemoney, 0)) worth19,\n" +
						"      SUM(multiIf(niff<19 , NULL, pay_diff >= 0 and pay_diff <= 19, fee + givemoney, 0)) worth20,\n" +
						"      SUM(multiIf(niff<20 , NULL, pay_diff >= 0 and pay_diff <= 20, fee + givemoney, 0)) worth21,\n" +
						"      SUM(multiIf(niff<21 , NULL, pay_diff >= 0 and pay_diff <= 21, fee + givemoney, 0)) worth22,\n" +
						"      SUM(multiIf(niff<22 , NULL, pay_diff >= 0 and pay_diff <= 22, fee + givemoney, 0)) worth23,\n" +
						"      SUM(multiIf(niff<23 , NULL, pay_diff >= 0 and pay_diff <= 23, fee + givemoney, 0)) worth24,\n" +
						"      SUM(multiIf(niff<24 , NULL, pay_diff >= 0 and pay_diff <= 24, fee + givemoney, 0)) worth25,\n" +
						"      SUM(multiIf(niff<25 , NULL, pay_diff >= 0 and pay_diff <= 25, fee + givemoney, 0)) worth26,\n" +
						"      SUM(multiIf(niff<26 , NULL, pay_diff >= 0 and pay_diff <= 26, fee + givemoney, 0)) worth27,\n" +
						"      SUM(multiIf(niff<27 , NULL, pay_diff >= 0 and pay_diff <= 27, fee + givemoney, 0)) worth28,\n" +
						"      SUM(multiIf(niff<28 , NULL, pay_diff >= 0 and pay_diff <= 28, fee + givemoney, 0)) worth29,\n" +
						"      SUM(multiIf(niff<29 , NULL, pay_diff >= 0 and pay_diff <= 29, fee + givemoney, 0)) worth30,\n" +
						"      SUM(multiIf(niff<44 , NULL, pay_diff >= 0 and pay_diff <= 44, fee + givemoney, 0)) worth45,\n" +
						"      SUM(multiIf(niff<59 , NULL, pay_diff >= 0 and pay_diff <= 59, fee + givemoney, 0)) worth60,\n" +
						"      SUM(multiIf(niff<74 , NULL, pay_diff >= 0 and pay_diff <= 74, fee + givemoney, 0)) worth75,\n" +
						"      SUM(multiIf(niff<89 , NULL, pay_diff >= 0 and pay_diff <= 89, fee + givemoney, 0)) worth90,\n" +
						"      SUM(multiIf(niff<119 , NULL, pay_diff >= 0 and pay_diff <= 119, fee + givemoney, 0)) worth120,\n" +
						"      SUM(multiIf(niff<149 , NULL, pay_diff >= 0 and pay_diff <= 149, fee + givemoney, 0)) worth150,\n" +
						"      SUM(multiIf(niff<179 , NULL, pay_diff >= 0 and pay_diff <= 179, fee + givemoney, 0)) worth180, ");
				payNSql.append(" SUM(sharfee) worthshar,\n");
				payNSql.append(" SUM(multiIf(niff<0 , NULL, pay_diff >= 0 and pay_diff <= 0, sharfee, 0)) worthshar1,\n" +
						"      SUM(multiIf(niff<1 , NULL, pay_diff >= 0 and pay_diff <= 1, sharfee, 0)) worthshar2,\n" +
						"      SUM(multiIf(niff<2 , NULL, pay_diff >= 0 and pay_diff <= 2, sharfee, 0)) worthshar3,\n" +
						"      SUM(multiIf(niff<3 , NULL, pay_diff >= 0 and pay_diff <= 3, sharfee, 0)) worthshar4,\n" +
						"      SUM(multiIf(niff<4 , NULL, pay_diff >= 0 and pay_diff <= 4, sharfee, 0)) worthshar5,\n" +
						"      SUM(multiIf(niff<5 , NULL, pay_diff >= 0 and pay_diff <= 5, sharfee, 0)) worthshar6,\n" +
						"      SUM(multiIf(niff<6 , NULL, pay_diff >= 0 and pay_diff <= 6, sharfee, 0)) worthshar7,\n" +
						"      SUM(multiIf(niff<7 , NULL, pay_diff >= 0 and pay_diff <= 7, sharfee, 0)) worthshar8,\n" +
						"      SUM(multiIf(niff<8 , NULL, pay_diff >= 0 and pay_diff <= 8, sharfee, 0)) worthshar9,\n" +
						"      SUM(multiIf(niff<9 , NULL, pay_diff >= 0 and pay_diff <= 9, sharfee, 0)) worthshar10,\n" +
						"      SUM(multiIf(niff<10 , NULL, pay_diff >= 0 and pay_diff <= 10, sharfee, 0)) worthshar11,\n" +
						"      SUM(multiIf(niff<11 , NULL, pay_diff >= 0 and pay_diff <= 11, sharfee, 0)) worthshar12,\n" +
						"      SUM(multiIf(niff<12 , NULL, pay_diff >= 0 and pay_diff <= 12, sharfee, 0)) worthshar13,\n" +
						"      SUM(multiIf(niff<13 , NULL, pay_diff >= 0 and pay_diff <= 13, sharfee, 0)) worthshar14,\n" +
						"      SUM(multiIf(niff<14 , NULL, pay_diff >= 0 and pay_diff <= 14, sharfee, 0)) worthshar15,\n" +
						"      SUM(multiIf(niff<15 , NULL, pay_diff >= 0 and pay_diff <= 15, sharfee, 0)) worthshar16,\n" +
						"      SUM(multiIf(niff<16 , NULL, pay_diff >= 0 and pay_diff <= 16, sharfee, 0)) worthshar17,\n" +
						"      SUM(multiIf(niff<17 , NULL, pay_diff >= 0 and pay_diff <= 17, sharfee, 0)) worthshar18,\n" +
						"      SUM(multiIf(niff<18 , NULL, pay_diff >= 0 and pay_diff <= 18, sharfee, 0)) worthshar19,\n" +
						"      SUM(multiIf(niff<19 , NULL, pay_diff >= 0 and pay_diff <= 19, sharfee, 0)) worthshar20,\n" +
						"      SUM(multiIf(niff<20 , NULL, pay_diff >= 0 and pay_diff <= 20, sharfee, 0)) worthshar21,\n" +
						"      SUM(multiIf(niff<21 , NULL, pay_diff >= 0 and pay_diff <= 21, sharfee, 0)) worthshar22,\n" +
						"      SUM(multiIf(niff<22 , NULL, pay_diff >= 0 and pay_diff <= 22, sharfee, 0)) worthshar23,\n" +
						"      SUM(multiIf(niff<23 , NULL, pay_diff >= 0 and pay_diff <= 23, sharfee, 0)) worthshar24,\n" +
						"      SUM(multiIf(niff<24 , NULL, pay_diff >= 0 and pay_diff <= 24, sharfee, 0)) worthshar25,\n" +
						"      SUM(multiIf(niff<25 , NULL, pay_diff >= 0 and pay_diff <= 25, sharfee, 0)) worthshar26,\n" +
						"      SUM(multiIf(niff<26 , NULL, pay_diff >= 0 and pay_diff <= 26, sharfee, 0)) worthshar27,\n" +
						"      SUM(multiIf(niff<27 , NULL, pay_diff >= 0 and pay_diff <= 27, sharfee, 0)) worthshar28,\n" +
						"      SUM(multiIf(niff<28 , NULL, pay_diff >= 0 and pay_diff <= 28, sharfee, 0)) worthshar29,\n" +
						"      SUM(multiIf(niff<29 , NULL, pay_diff >= 0 and pay_diff <= 29, sharfee, 0)) worthshar30,\n" +
						"      SUM(multiIf(niff<44 , NULL, pay_diff >= 0 and pay_diff <= 44, sharfee, 0)) worthshar45,\n" +
						"      SUM(multiIf(niff<59 , NULL, pay_diff >= 0 and pay_diff <= 59, sharfee, 0)) worthshar60,\n" +
						"      SUM(multiIf(niff<74 , NULL, pay_diff >= 0 and pay_diff <= 74, sharfee, 0)) worthshar75,\n" +
						"      SUM(multiIf(niff<89 , NULL, pay_diff >= 0 and pay_diff <= 89, sharfee, 0)) worthshar90,\n" +
						"      SUM(multiIf(niff<119 , NULL, pay_diff >= 0 and pay_diff <= 119, sharfee, 0)) worthshar120,\n" +
						"      SUM(multiIf(niff<149 , NULL, pay_diff >= 0 and pay_diff <= 149, sharfee, 0)) worthshar150,\n" +
						"      SUM(multiIf(niff<179 , NULL, pay_diff >= 0 and pay_diff <= 179, sharfee, 0)) worthshar180 ");
			}
		}
		payNSql.append(" from ( \n");
		payNSql.append(this.getPaySql(req));
		payNSql.append(" ) \n");
		payNSql.append(" where 1=1  \n");

		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			payNSql.append(" AND investor IN (").append(req.getUserIds()).append(")");
		}

		payNSql.append(this.selectCondition(req, " "));
		payNSql.append(" group by  \n");
		payNSql.append(this.getGroupColumnSql(req));
		return payNSql;
	}

	// 充值金额  N日充值金额
	private StringBuilder getPaySql(AdRecoveryDto req) {
		StringBuilder paySql = new StringBuilder();

		paySql.append(" select \n");
		paySql.append(" argMax(reg.day, reg.receivetime) day, \n");
		paySql.append(this.getPeriodSql(req));
		paySql.append(" argMax(rc.day, reg.receivetime) payday, \n");
		paySql.append(" argMax(rc.uuid, reg.receivetime) AS uuid, \n");
		paySql.append(" argMax(rc.fee, reg.receivetime) fee, \n");
		paySql.append(" argMax(rc.givemoney, reg.receivetime) givemoney, \n");
		paySql.append(" argMax(rc.sharfee, reg.receivetime) sharfee, \n");
		paySql.append(" argMax(rc.createtime, reg.receivetime) createtime, \n");
		paySql.append(" argMax(reg.gameid, reg.receivetime) AS gameid, \n");
		paySql.append(" argMax(ad.adid, reg.receivetime) adidTmp, \n");
		paySql.append(" (case when adidTmp is not null THEN adidTmp else '' end) as adid, \n");
		paySql.append(" argMax(reg.os, reg.receivetime) AS os, \n");
		paySql.append(" argMax(reg.pgid, reg.receivetime) pgid, \n");
		paySql.append(" argMax(reg.kid, reg.receivetime) regkid, \n");

		paySql.append(" argMax(wpc.manage,reg.receivetime) investor, \n");
		paySql.append(" argMax(osu.dept_id, reg.receivetime) deptId, \n");
		paySql.append(" IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId, \n"); // 组别
		paySql.append(" IFNULL(argMax(osdg.name, reg.receivetime),'') userGroupName, \n"); // 组别名
		paySql.append(" argMax(osd.name, reg.receivetime) deptName, \n");
		paySql.append(" argMax(osu.real_name, reg.receivetime) investorName, \n");

		paySql.append(" argMax(reg.parentchl, reg.receivetime) parentchl, \n");
		paySql.append(" argMax(reg.chl, reg.receivetime) chl, \n");
		paySql.append(" argMax(reg.appchl, reg.receivetime) appchl, \n");
		paySql.append(" dateDiff('day', parseDateTimeBestEffort(toString(day)),  today() ) as niff, \n");
		paySql.append(" dateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(payday))) as pay_diff \n");
		paySql.append(" from v_original_user_recharge_share rc \n");
		paySql.append(" LEFT join thirty_game_device_reg reg \n");
		paySql.append(" on rc.uuid=reg.uuid \n");
		paySql.append(" and rc.pgid = reg.pgid \n");
		paySql.append(" LEFT JOIN v_thirty_ad_device ad \n");
		paySql.append(" on reg.uuid = ad.uuid \n");
		paySql.append(" and reg.day = ad.day \n");
		paySql.append(" and reg.pgid = ad.pgid \n");

		paySql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		paySql.append("  ON wpc.isdelete = 0 \n");
		paySql.append("  AND wpc.parent_code = reg.parentchl \n");
		paySql.append("  AND wpc.chncode = reg.chl \n");

		paySql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		paySql.append("  wpc.manage = osu.user_id \n");
		paySql.append("  left join odsmysql_sys_dept osd \n");
		paySql.append("   on osu.dept_id = osd.dept_id \n");
		paySql.append("  left join odsmysql_sys_dept_group osdg \n");
		paySql.append("   on osu.dept_group_id = osdg.id \n");

		paySql.append("  LEFT JOIN v_odsmysql_adid vad  -- 广告计划表 \n");
		paySql.append(" ON ad.adid = vad.adid \n");
		paySql.append(" AND ad.ctype = vad.ctype \n");


		paySql.append(" where 1=1  ");
//		paySql.append(this.getAuthSql(req));
		paySql.append(" and reg.day  >=").append(req.getSTime()).append(" \n ");
		paySql.append(" and reg.day  <=").append(req.getETime()).append(" \n ");
		paySql.append(" and rc.day >=reg.day \n");
		paySql.append(" group by rc.kid \n");
		return paySql;
	}

	//新增设备注册数、新增设备数
	private StringBuilder getDeviceSql(AdRecoveryDto req) {
		StringBuilder uuidSql = new StringBuilder();
		uuidSql.append(" SELECT \n");

		//添加查询维度
		uuidSql.append(this.getQueryColumnSql(req));
		uuidSql.append(" COUNT(DISTINCT reg.kid) uuidnums, --新增设备数 \n");
		uuidSql.append(" COUNT(DISTINCT ud.dr_kid) usrnamenums --新增设备注册数 \n ");
		uuidSql.append("from ( \n ");
		uuidSql.append(" SELECT reg.day day, \n ");
		uuidSql.append(this.getPeriodSql(req));

		uuidSql.append(" reg.uuid uuid,reg.gameid gameid,reg.kid kid,reg.receivetime receivetime, ");
		uuidSql.append(" reg.pgid pgid,reg.parentchl parentchl,reg.chl chl,reg.os os,reg.appchl appchl,ad.adid adid,ad.advert_id advert_id, \n ");

		uuidSql.append(" wpc.manage investor, \n ");
		uuidSql.append(" osu.real_name investorName, \n ");
		uuidSql.append(" osu.dept_id deptId,  \n ");
		uuidSql.append(" IFNULL(osu.dept_group_id,0) userGroupId,\n "); // 组别
		uuidSql.append(" IFNULL(osdg.name,'') userGroupName,\n "); // 组别名
		uuidSql.append(" osd.name deptName  \n ");

		uuidSql.append(" from thirty_game_device_reg reg \n ");
		uuidSql.append(" LEFT JOIN v_thirty_ad_device ad \n ");
		uuidSql.append(" on reg.uuid = ad.uuid \n ");
		uuidSql.append(" and reg.pgid = ad.pgid \n ");
		uuidSql.append(" and reg.day = ad.`day`  \n ");
		uuidSql.append(this.getAuthSql(req));
		uuidSql.append(" ) reg \n ");

		uuidSql.append(" LEFT JOIN ( \n ");
		uuidSql.append(" select \n ");
		uuidSql.append(" argMax(ur.kid, dr.receivetime) ur_kid, \n ");
		uuidSql.append(" argMax(dr.kid, dr.receivetime) dr_kid, \n ");
		uuidSql.append(" argMax(ur.day, dr.receivetime) day, \n ");
		uuidSql.append(" argMax(ur.uuid, dr.receivetime) uuid, \n ");
		uuidSql.append(" argMax(ur.usrname, dr.receivetime) usrname, \n ");
		uuidSql.append(" argMax(ur.gameid, dr.receivetime) gameid, \n ");
		uuidSql.append(" argMax(dr.parentchl, dr.receivetime) parentchl, \n ");
		uuidSql.append(" argMax(dr.chl, dr.receivetime) chl, \n ");
		uuidSql.append(" argMax(dr.appchl, dr.receivetime) appchl \n ");
		uuidSql.append(" from ( \n ");
		uuidSql.append(" select a.kid kid,a.`day` day,a.uuid uuid,a.usrname usrname, \n ");
		uuidSql.append(" a.gameid gameid,g.pgid pgid,a.receivetimes receivetimes \n ");
		uuidSql.append(" from v_game_account_reg a \n ");
		uuidSql.append(" left join odsmysql_wan_game g \n ");
		uuidSql.append(" on a.gameid = g.id) ur\n ");
		uuidSql.append(" LEFT join thirty_game_device_reg  dr \n ");
		uuidSql.append(" on ur.uuid = dr.uuid \n ");
		uuidSql.append(" and ur.pgid = dr.pgid \n ");

//		uuidSql.append(" and ur.day =dr.`day` \n ");

		uuidSql.append("   where 1=1  \n");
		uuidSql.append("  and  ur.receivetimes >= dr.receivetime \n");
		uuidSql.append(" and dr.day >= ").append(req.getSTime()).append(" \n ");
		uuidSql.append(" and dr.day <=  \n").append(req.getETime()).append(" \n ");
		uuidSql.append("  group by ur.kid  \n");
		uuidSql.append("  )ud  \n");
		uuidSql.append("  on reg.kid = ud.dr_kid  \n");
		uuidSql.append("   where 1=1  \n");
		uuidSql.append(this.selectCondition(req, "reg"));
		uuidSql.append("  group by ");
		uuidSql.append(this.getGroupColumnSql(req));
		return uuidSql;
	}

	//周期 周月
	private StringBuilder getPeriodSql(AdRecoveryDto req) {

		StringBuilder periodSq = new StringBuilder();
		periodSq.append(" parseDateTimeBestEffort(toString(day)) dayDate,  \n");
		periodSq.append(" toString(toYear(dayDate)) yearStr,  \n");
		periodSq.append("  toMonth(dayDate) monthInt,  \n");
//		periodSq.append("  toWeek(dayDate) weekInt,  \n");
		periodSq.append("  toYearWeek(dayDate,3) weekInt,  \n");
		periodSq.append("  concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS month,  \n");
		periodSq.append("  concat(IF(10>weekInt, '0', ''), toString(weekInt), '周') AS week,  \n");
//		periodSq.append("  concat(yearStr, '-', IF(10>weekInt, '0', ''), toString(weekInt), '周') AS week,  \n");
//		periodSq.append(" concat(toString(toYear(parseDateTimeBestEffort(toString(day)))),'-',toString(toWeek(parseDateTimeBestEffort(toString(day)))),'周') week, \n ");
//		periodSq.append(" concat(toString(toYear(parseDateTimeBestEffort(toString(day)))),'-',toString(toMonth(parseDateTimeBestEffort(toString(day)))),'月') week, \n ");
		periodSq.append(" yearStr as year, ");
		periodSq.append(req.getPeriod()).append(" as period, ");

		return periodSq;
	}

	//权限关联表
	private StringBuilder getAuthSql(AdRecoveryDto req) {
		StringBuilder authSql = new StringBuilder();
		authSql.append(" LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc  -- 渠道表 \n");
		authSql.append("  ON wpc.isdelete = 0 \n");
		authSql.append("  AND wpc.parent_code = reg.parentchl \n");
		authSql.append("  AND wpc.chncode = reg.chl \n");

		authSql.append("  LEFT JOIN odsmysql_sys_user osu ON \n");
		authSql.append("  wpc.manage = osu.user_id \n");
		authSql.append("  left join odsmysql_sys_dept osd \n");
		authSql.append("   on osu.dept_id = osd.dept_id \n");
		authSql.append("  left join odsmysql_sys_dept_group osdg \n");
		authSql.append("   on osu.dept_group_id = osdg.id \n");

		authSql.append("  LEFT JOIN v_odsmysql_adid vad  -- 广告计划表 \n");
		authSql.append(" ON ad.adid = vad.adid \n");
		authSql.append(" AND ad.ctype = vad.ctype \n");
		authSql.append(" WHERE 1=1 \n");
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {

			// -- 渠道权限
			authSql.append(" AND \n");
			authSql.append(" wpc.manage IN ( -- 管理的账号 \n");
			authSql.append(req.getUserIds());
			authSql.append(" ) \n");
		}
		return authSql;
	}

	//参数类别判断

	//类别参数
	private StringBuilder getQueryColumnSql(AdRecoveryDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder periodColumnSql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		periodColumnSql.append(" period ");
		queryColumnSql.append(",");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName");
				queryColumnSql.append(",");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName");
				queryColumnSql.append(",");
			}
			queryColumnSql.append(queryColumn);
			queryColumnSql.append(",");
		}
		sql.append(periodColumnSql).append(queryColumnSql);
		return sql;
	}

	//分组条件
	private StringBuilder getGroupColumnSql(AdRecoveryDto req) {
		String queryColumn = req.getQueryColumn();

		StringBuilder sql = new StringBuilder();
		StringBuilder periodColumnSql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();
		periodColumnSql.append(" period ");

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("deptName");

			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(",");
				groupColumnSql.append("investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(",");
				groupColumnSql.append("userGroupName");

			}
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
		}
		sql.append(periodColumnSql).append(groupColumnSql);
		return sql;
	}

	//筛选条件
	public String selectCondition(AdRecoveryDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//组别
		String userGroupIdArr = req.getUserGroupIdArr();
		//投放人
		String investorArr = req.getInvestorArr();
		//投放人
		String gameidArr = req.getGameidArr();
		//日期
		Long sTime = req.getSTime();
		Long eTime = req.getETime();
		if (StringUtils.isBlank(bieming)) {

			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append("  AND deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and day  >= ").append(sTime);
				sqlCondition.append(" and day  <= ").append(eTime);
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and ").append(bieming).append(".day  >= ").append(sTime);
				sqlCondition.append(" and ").append(bieming).append(".day  <= ").append(eTime);
			}

			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and ").append(bieming).append(".os  = " + os);
			}
			return sqlCondition.toString();
		}
	}

	public static void main(String[] args) {
		BigDecimal worth1 = new BigDecimal(20.16);
		BigDecimal worth2 = new BigDecimal(30.000);
		System.out.println(worth1.divide(worth2, 2, RoundingMode.HALF_UP));
	}
}
